package testCase.java.druid;


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;

import org.junit.Test;

import com.alibaba.druid.pool.DruidDataSource;


/**
 * 
 * @author lucky
 *         https://github.com/alibaba/druid/wiki/%E5%B8%B8%E8%A7%81%E9%97%AE
 *         %E9%A2%98
 *         http://blog.csdn.net/tower888/article/details/8960971
 *         http://lj6684.iteye.com/blog/1770093
 */
public class sample {
	private static final int COUNT = 5;

	@Test
	public void test() throws SQLException {
		createTable();
		DruidDataSource db = createDB();

		StringBuffer ddl = new StringBuffer();
		ddl.append("INSERT INTO t_big (");
		for (int i = 0; i < COUNT; ++i) {
			if (i != 0) {
				ddl.append(", ");
			}
			ddl.append("F" + i);
		}
		ddl.append(") VALUES (");
		for (int i = 0; i < COUNT; ++i) {
			if (i != 0) {
				ddl.append(", ");
			}
			ddl.append("?");
		}
		ddl.append(")");
		Connection conn = db.getConnection();
		System.out.println(ddl.toString());
		PreparedStatement stmt = conn.prepareStatement(ddl.toString());
		for (int i = 0; i < COUNT; ++i) {
			stmt.setInt(i + 1, i);
		}
		stmt.execute();
		stmt.close();
		conn.close();
	}

	private DruidDataSource createDB() throws SQLException {
		// define dataSource
		DruidDataSource dataSource = new DruidDataSource();
		dataSource.setDriverClassName("org.postgresql.Driver");
		dataSource.setUsername("daxuu");
		dataSource.setPassword("ayd");
		dataSource.setUrl("jdbc:postgresql://127.0.0.1/daxuu");
		dataSource.setInitialSize(5);
		dataSource.setMinIdle(1);
		dataSource.setMaxActive(10);
		// 启用监控统计功能
		dataSource.setFilters("stat");// for mysql
		dataSource.setPoolPreparedStatements(false);
		//dataSource.set

		return dataSource;
	}

	// DruidPooledConnection conn = dataSource.getConnection();

	@SuppressWarnings("unused")
	private void dropTable() throws SQLException {

		DruidDataSource db = createDB();

		Connection conn = db.getConnection();
		Statement stmt = conn.createStatement();
		stmt.execute("DROP TABLE t_big");
		stmt.close();
		conn.close();
	}

	public void createTable() throws SQLException {

		DruidDataSource db = createDB();
		// db.create
		StringBuffer ddl = new StringBuffer();
		ddl.append("CREATE TABLE t_big (FID bigserial");
		for (int i = 0; i < COUNT; ++i) {
			ddl.append(", ");
			ddl.append("F" + i);
			ddl.append(" varchar(10)");
		}
		ddl.append(")");
		Connection conn = db.getConnection();
		Statement stmt = conn.createStatement();
		System.out.println(ddl.toString());
		stmt.execute(ddl.toString());
		stmt.close();
		conn.close();
	}

}
